/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 */
public class DBProduct implements IFDBProduct {
    private Connection con;

    public DBProduct() {
	con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList getAllProducts(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    
    public Product findProduct(String id, boolean retriveAssociation)
    {   String wClause = "  id = " + id;
        return singleWhere(wClause, retriveAssociation);
    }
    
    public Product searchProductName(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    public int insertProduct(Product prod)
    {  
        int nextID = GetMax.getMaxId("Select max(id) from Product");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);

       int rc = -1;
	   String query="INSERT INTO Product(id, name, purchasePrice," + "" +
		   "salesPrice, rentPrice, countryOfOrigin, minStock," + "" +
		   "supplierId)  VALUES("+
	  		nextID + ",'" +
                        prod.getName()  + "',"  +
		    	prod.getPurchasePrice()  + ","  +
                        prod.getSalesPrice() + ",'" +
			prod.getRentPrice() + "','" +
			prod.getCountryOfOrigin() + "'," +
			prod.getMinStock() + "," +
			prod.getSupplierId() +
			")";


      System.out.println("insert : " + query);
      try{
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in product db: "+ex);
	   }
	   return(rc);
	}

    	public int updateProduct(Product prod)
	{
		int rc=-1;

		String query="UPDATE Product SET "+
		 	  "name ='"+ prod.getName() +"', "+
		 	  "purchasePrice ="+ prod.getPurchasePrice() +", "+
		 	  "salesPrice ="+ prod.getSalesPrice() +", "+
		 	  "rentPrice ="+ prod.getRentPrice() +", "+
		 	  "countryOfOrigin ='"+ prod.getCountryOfOrigin() +"', "+
		 	  "minStock ='"+ prod.getMinStock() +"', "+
		 	  "supplierId ='"+ prod.getSupplierId() +"'"+
		          " WHERE id = "+ prod.getId();
                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}

	public int delete(int id)
	{
               int rc=-1;

	  	String query="DELETE FROM Product WHERE id =" +
				id;
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
		return(rc);
	}

	private ArrayList miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList list = new ArrayList();

		String query =  buildQuery(wClause);

  		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);


			while( results.next() ){
			 Product prod = new Product();
			 prod = buildProduct(results);
                         list.add(prod);
			}
			stmt.close();
		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Product prod = new Product();
//        empObj.setId(0);
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            prod = buildProduct(results);
                            
                            stmt.close();
			}

		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return prod;
	}

	private String buildQuery(String wClause)
	{
	    String query="Select * from Product";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an customer object
	private Product buildProduct(ResultSet results)
      {   Product prod = new Product();
          try{
                prod.setId(results.getInt(1));
                prod.setName(results.getString(2));
                prod.setPurchasePrice(results.getFloat(3));
                prod.setSalesPrice(results.getFloat(4));
                prod.setRentPrice(results.getDouble(5));
                prod.setCountryOfOrigin(results.getString(6));
                prod.setMinStock(results.getInt(7));
		prod.setSupplierId(results.getInt(8));

          }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return prod;
      }
}
